package dblayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import modellayer.PartSale;

public class DBPartSale implements IFDBPartSale {

	private Connection con;
	private DBItem item;
	private DBSale sale;
	
	public DBPartSale(){
		con = DBConnection.getInstance().getDBcon();
		item = new DBItem();
		sale = new DBSale();
	}
	
	@Override
	public ArrayList<PartSale> getAllPartSale() {
		// TODO Auto-generated method stub
		return miscWhere("");
	}

	@Override
	public PartSale searchByID(int id) {
		// TODO Auto-generated method stub
		String query = " id = '" + id + "'";
		return singleWhere(query);
	}

	@Override
	public ArrayList<PartSale> searchBySaleID(int saleID) {
		// TODO Auto-generated method stub
		String query = " saleID = '" + saleID + "'";
		return miscWhere(query);
	}

	@Override
	public PartSale searchByVareID(int itemID) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int insertPartSale(PartSale sale) throws Exception {
		// TODO Auto-generated method stub
		int nextID = GetMax.getMaxID("SELECT max(id) FROM Part_Sale");
		nextID = nextID + 1;
		
		int rc = -1;
		
		String query = "INSERT INTO Part_Sale(id, saleID ,itemID, amount) VALUES('"
				+ nextID + "','"
				+ sale.getSale().getID() + "','"
				+ sale.getItem().getID() + "','"
				+ sale.getAmount() + "')";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(SQLException e){
			System.out.println("Error : Exception catched in insertPartSale. " + e);
			e.printStackTrace();
		}
		
		return rc;
	}

	@Override
	public int updatePartSale(PartSale sale) {
		// TODO Auto-generated method stub
		int rc = -1;
		
		String query = "UPDATE Part_Sale SET "
				+ "saleID = '" + sale.getSale().getID() + "',"
				+ "itemID = '" + sale.getItem().getID() + "',"
				+ "amount = '" + sale.getAmount() + "'"
				+ " WHERE  id = '" + sale.getID() + "'";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.out.println("Error : Exception catched in updatePartSale. " + e);
			e.printStackTrace();
		}
		
		return rc;
	}

	@Override
	public int deletePartSale(int id) {
		// TODO Auto-generated method stub
		int rc = -1;
		
		String query = "DELETE FROM Part_Sale WHERE id = '" + id + "'";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.out.println("Error : Exception catched in deletePartSale. " + e);
			e.printStackTrace();
		}
		
		return rc;
	}

	/////////////////////////////////////////////////////////////////////////////////////////
	/////////////////////////// Methods outside the interface ///////////////////////////////
	/////////////////////////////////////////////////////////////////////////////////////////
	
	/**
	 * 
	 */
	private ArrayList<PartSale> miscWhere(String wClause){
		ResultSet results;
	    ArrayList<PartSale> list = new ArrayList<PartSale>();	
		
	    String query =  buildQuery(wClause);
  
        try{
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);
	 	
	
        	while( results.next() ){
        		PartSale city = new PartSale();
        		city = buildPartSale(results);	
        		list.add(city);	
        	}
        	
            stmt.close();
			
		}
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: " + e);
			e.printStackTrace();
	 	}
        
		return list;
	}
	
	/**
	 * 
	 */
	private PartSale singleWhere(String wClause){
		ResultSet results;
		
		PartSale del = new PartSale();
		
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
			{
				del = buildPartSale(results);
				stmt.close();
			}
			else
			{
				del = null;
			}
		}
		catch(Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		
		return del;
	}
	
	/**
	 * 
	 */
	private String buildQuery(String wClause){
		String query="SELECT id, saleID, itemID, amount FROM Part_Sale";
		
		if (wClause.length()>0)
			query = query + " WHERE " + wClause;
			
		return query;
	}
	
	/**
	 * 
	 */
	private PartSale buildPartSale(ResultSet result){
		PartSale partSale = new PartSale();
		
		try{
			partSale.setID(result.getInt("id"));
			partSale.setSale(sale.searchByID(result.getInt("saleID")));
			partSale.setItem(item.searchByID(result.getInt("itemID")));
			partSale.setAmount(result.getInt("amount"));
		}
		catch(Exception e){
			System.out.println("Error : Exception catched in buildPartSale. " + e);
			e.printStackTrace();
		}
		
		return partSale;
	}
}
